package es.uv.bd.model;

import static es.uv.bd.model.BodegasDAO.DBURL;
import static es.uv.bd.model.BodegasDAO.DRIVER;
import static es.uv.bd.model.BodegasDAO.PASSWORD;
import static es.uv.bd.model.BodegasDAO.USERNAME;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Iterator;
import javax.swing.table.DefaultTableModel;

public class BodegasDAO {    
    /*
     * Parámetros de conexión a la base de datos
     */
    public static final String DRIVER = "oracle.jdbc.OracleDriver";
    public static final String DBURL = "jdbc:oracle:thin:@pokemon2.uv.es:1521:ORCL";
    public static final String USERNAME = "AL001";
    public static final String PASSWORD = "AL001";
    

    private static final String CREATE = 
            "INSERT INTO bodega (cod_bodega, nombre, pais, " +
            "nombre_den_o, email, anyo, region, web, telefono, codigo_postal, "+
            "direccion, enologo) " +
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
    
    private static final String READ = 
            "SELECT COD_BODEGA, NOMBRE, PAIS, NOMBRE_DEN_O, EMAIL, ANYO, REGION, WEB, TELEFONO, CODIGO_POSTAL, DIRECCION, ENOLOGO "+
            "  FROM bodega " +
            " WHERE COD_BODEGA = ?";
    
    
    /* Hacer Los cambios pertinentes */
    
    private static final String READALL = 
            "SELECT b.COD_BODEGA, b.NOMBRE, b.PAIS, b.NOMBRE_DEN_O, den.descripcion,  b.EMAIL, b.ANYO, b.REGION, b.WEB, b.TELEFONO, b.CODIGO_POSTAL, b.DIRECCION, b.ENOLOGO "+
            " FROM BODEGA b left outer join DENOMINACION_DE_ORIGEN den on b.NOMBRE_DEN_O = den.nombre" +
            " " +
            " ORDER BY b.cod_bodega";
    
    private static final String FK_key = 
            "SELECT den.NOMBRE "+
            " FROM DENOMINACION_DE_ORIGEN den " +
            " " +
            " ORDER BY den.NOMBRE";
    
    private static final String UPDATE =
            "UPDATE bodega " +
            "   SET  NOMBRE= ?, PAIS= ?, NOMBRE_DEN_O= ?, EMAIL= ?, ANYO= ?, REGION= ?, WEB= ?, TELEFONO= ?, CODIGO_POSTAL= ?, DIRECCION= ?, ENOLOGO = ?" +
            " WHERE COD_BODEGA = ?";
    
    private static final String DELETE =
            "DELETE FROM bodega " +
            " WHERE COD_BODEGA = ?";
    
    public String[] fk_Key(){
        
    // DefaultTableModel tablaBodega= new DefaultTableModel();
        ArrayList<String> key = new ArrayList<String>();            
        try {
            /*
            * Conexion a la base de datos
            */
            Class.forName(DRIVER).newInstance();
            Connection oracleConn = DriverManager.getConnection(DBURL,USERNAME,PASSWORD);
            PreparedStatement s = oracleConn.prepareStatement(FK_key);
            ResultSet rs = s.executeQuery();
            
            while (rs.next()) {
                key.add(rs.getString("NOMBRE"));  
            }
            oracleConn.close();
        }
        catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e) {
            System.out.println("BodegasDAO::getString[]KEY -- " + e.getMessage());
        }
        finally {
            String[] fk = new String[key.size()] ;
            fk = key.toArray(fk);
            return fk;
        }
    }
    
    public void crearBodega(Bodegas bode) throws 
            ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        
        /*
        * Conexion a la base de datos
        */
        Class.forName(DRIVER).newInstance();
        Connection oracleConn = DriverManager.getConnection(DBURL,USERNAME,PASSWORD);
        oracleConn.setAutoCommit(false);
        // Sentencia de insert
        
        PreparedStatement insert = oracleConn.prepareStatement(CREATE);
        
        insert.setInt(1, bode.getCod_bodega());
        insert.setString(2, bode.getNombre());
        insert.setString(3, bode.getPais());
        insert.setString(4, bode.getNombre_den_orig());
        insert.setString(5, bode.getEmail());
        insert.setInt(6, bode.getAnyo());
        insert.setString(7, bode.getRegion());
        insert.setString(8, bode.getWeb());
        insert.setInt(9, bode.getTelefono());
        insert.setInt(10, bode.getCod_postal());
        insert.setString(11, bode.getDireccion());
        insert.setString(12, bode.getEnologo());
        insert.executeUpdate();
        
        oracleConn.commit();
        oracleConn.setAutoCommit(true);
        oracleConn.close();
    }
    
    public Bodegas leerBodega(int cod_Bod) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException, ParseException {
        
        Bodegas bod = new Bodegas();
        //SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
      
        Class.forName(DRIVER).newInstance();
        Connection oracleConn = DriverManager.getConnection(DBURL,USERNAME,PASSWORD);
           
        // Sentencia de insert
        PreparedStatement read = oracleConn.prepareStatement(READ);
        read.setInt(1, cod_Bod);
        ResultSet rs = read.executeQuery();
        
        if (rs.next()) {
            
            bod.setCod_bodega(rs.getInt("COD_BODEGA"));
            bod.setNombre(rs.getString("NOMBRE"));
            bod.setPais(rs.getString("PAIS"));
            bod.setNombre_den_orig(rs.getString("NOMBRE_DEN_O"));
            bod.setEmail(rs.getString("EMAIL"));
            bod.setAnyo(rs.getInt("ANYO"));
            bod.setRegion(rs.getString("REGION"));
            bod.setWeb(rs.getString("WEB"));
            bod.setTelefono(rs.getInt("TELEFONO"));
            bod.setCod_postal(rs.getInt("CODIGO_POSTAL"));
            bod.setDireccion(rs.getString("DIRECCION"));
            bod.setEnologo(rs.getString("ENOLOGO"));
        }
        return bod;
    }
    
    public void actualizarBodega(Bodegas bode) throws ClassNotFoundException, 
           InstantiationException, IllegalAccessException, SQLException {
           
        /*
        * Conexion a la base de datos
        */
        Class.forName(DRIVER).newInstance();
        Connection oracleConn = DriverManager.getConnection(DBURL,USERNAME,PASSWORD);
           
        oracleConn.setAutoCommit(false);
        // Sentencia de insert
        PreparedStatement update = oracleConn.prepareStatement(UPDATE);
        
        
        update.setString(1, bode.getNombre());
        update.setString(2, bode.getPais());
        update.setString(3, bode.getNombre_den_orig());
        update.setString(4, bode.getEmail());
        update.setInt(5, bode.getAnyo());
        update.setString(6, bode.getRegion());
        update.setString(7, bode.getWeb());
        update.setInt(8, bode.getTelefono());
        update.setInt(9, bode.getCod_postal());
        update.setString(10, bode.getDireccion());
        update.setString(11, bode.getEnologo());
        update.setInt(12, bode.getCod_bodega());
        update.executeUpdate();
        
        oracleConn.commit();
        oracleConn.setAutoCommit(true);
        oracleConn.close();
    }
    
    public void borrarBodega(int cod_bod) throws ClassNotFoundException, 
           InstantiationException, IllegalAccessException, SQLException {
        /*
        * Conexion a la base de datos
        */
        Class.forName(DRIVER).newInstance();
        Connection oracleConn = DriverManager.getConnection(DBURL,USERNAME,PASSWORD);
            
        oracleConn.setAutoCommit(false);
        
        // Sentencia de borrado
        PreparedStatement delete = oracleConn.prepareStatement(DELETE);
        delete.setInt(1, cod_bod);
        delete.executeUpdate();
        
        oracleConn.commit();
        oracleConn.setAutoCommit(true);
        oracleConn.close();
    }

    public DefaultTableModel getTablaBodegas() {
        
        DefaultTableModel tablaBodega= new DefaultTableModel();
        
        try {
            /*
            * Conexion a la base de datos
            */
            Class.forName(DRIVER).newInstance();
            Connection oracleConn = DriverManager.getConnection(DBURL,USERNAME,PASSWORD);
            
            PreparedStatement s = oracleConn.prepareStatement(READALL);
            ResultSet rs = s.executeQuery();
            ResultSetMetaData rsMd = rs.getMetaData();
            //La cantidad de columnas que tiene la consulta
            int numeroColumnas = rsMd.getColumnCount();
            //Establecer como cabezeras el nombre de las colimnas
            for (int i = 1; i <= numeroColumnas; i++) {
                tablaBodega.addColumn(rsMd.getColumnLabel(i));
            }
            //Creando las filas para el JTable
            while (rs.next()) {
                Object[] fila = new Object[numeroColumnas];
                fila[ 0 ] = rs.getInt("COD_BODEGA");
                fila[ 1 ] = rs.getString("NOMBRE");
                fila[ 2 ] = rs.getString("PAIS");
                fila[ 3 ] = rs.getString("NOMBRE_DEN_O");
                fila[ 4 ] = rs.getString("descripcion");
                fila[ 5 ] = rs.getString("EMAIL");
                fila[ 6 ] = rs.getInt("ANYO");
                fila[ 7 ] = rs.getString("REGION");
                fila[ 8 ] = rs.getString("WEB");
                fila[ 9 ] = rs.getInt("TELEFONO");
                fila[ 10 ] = rs.getInt("CODIGO_POSTAL");
                fila[ 11 ] = rs.getString("DIRECCION");
                fila[ 12 ] = rs.getString("ENOLOGO");
                tablaBodega.addRow(fila);
            }
            oracleConn.close();
        }
        catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e) {
            System.out.println("MascotasDAO::getTablaMascotas -- " + e.getMessage());
        }
        finally {
            return tablaBodega;
        }
    }
}

    

